package com.yulian.repository;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

import java.util.Iterator;
import java.util.Set;

public class GoodsClassRepositoryImpl {
	final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsClassRepositoryImpl.class);


	//第二步 用获取到的链接查询数据库  getGoodsClasses
	public Future<RowSet<Row>> getGoodsClasses(SqlConnection conn, Integer page, Integer size){
	    Promise<RowSet<Row>> promise = Promise.promise();
	    conn
	      .preparedQuery("select class_code,class_name,pcode,image_path from goods_class where class_level=3 and is_delete=0 limit  $2 offset $1")
	      .execute(Tuple.of(page, size), ar2 -> {
	        //Release the connection to the pool
	        conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getGoodsClasses, query success: "+ ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getGoodsClasses, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}
	//第二步 用获取到的链接查询数据库  getGoodsVarieties
	public Future<RowSet<Row>> getGoodsVarieties(SqlConnection conn, String pCode, Integer page, Integer size){
	    Promise<RowSet<Row>> promise = Promise.promise();
	    conn
	      .preparedQuery("select class_code,class_name,pcode,image_path from goods_class where class_level=4 and is_delete=0 and pCode = $1 limit  $3 offset $2")
	      .execute(Tuple.of(pCode, page, size), ar2 -> {
	        //Release the connection to the pool
	        conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getGoodsVarieties, query success: "+ ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getGoodsVarieties, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}




	public Future<RowSet<Row>> getRecordsTotal(SqlConnection conn, JsonObject queryJSObj){
		Promise<RowSet<Row>> promise = Promise.promise();
		//首先取出符合条件的总的记录数
     	String sql = this.creaStrGetRecordsTotalWithJson(queryJSObj) ;
	    conn
	      .preparedQuery(sql)
	      .execute( ar2 -> {
	        //Release the connection to the pool
	        //conn.close();
	        if(ar2.succeeded()){
//	          logger.info("getRecordsTotal, query success: "+ ar2.result().rowCount());
	          promise.complete(ar2.result());
	        }else{
	        	logger.info("getRecordsTotal, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}

	// 创建查询符合搜索条件的记录数的 sql语句
	public String creaStrGetRecordsTotalWithJson(JsonObject queryJS) {

		// define SelectQuery object
		SelectQuery sq = new SelectQuery();
		sq.addCustomColumns(new CustomSql(" count(*) as total ") )
		    .addCustomFromTable("goods_class");

		//common part to add InCondition
		Iterator<String> iter =  queryJS.fieldNames().iterator();
		while (iter.hasNext()) {
			String key =  iter.next();
			try {
				String value = queryJS.getString(key);
//						  logger.info(key+ ": "+ value.toString());
				if(!value.equals("") && !value.equals("[]") ) {
					String firstChar = value.substring(0,1);
					if(firstChar.equals("["))
						value = value.substring(1,value.length()-1);
					value = value.replace(", ", "','");
					//添加where  InCondtion 子句
					sq.getWhereClause().addCustomCondition(new InCondition( new CustomSql(key),  value));
			  }

			} catch (Exception e) {
				// Something went wrong!
			}
		}
	   	return sq.validate().toString()  ;

	}

	public Future<RowSet<Row>> getRecords(SqlConnection conn, JsonObject queryJSObj,int query_offset,int query_limit){
		Promise<RowSet<Row>> promise = Promise.promise();
		//取出满足条件的记录
		String sql = this.creaStrGetRecordsWithJson(queryJSObj,query_offset, query_limit) ;
	    conn
	    .preparedQuery(sql)
	    .execute( ar2 -> {
	    	//Release the connection to the pool
	    	//conn.close();
	        if(ar2.succeeded()){
	        	logger.info("getRecords, query success: "+ ar2.result().rowCount());
	        	promise.complete(ar2.result());
	        }else{
	        	logger.info("getRecords, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	    });
	    return promise.future();
	}

	// 创建查询符合搜索条件记录 的sql语句
	public String creaStrGetRecordsWithJson(JsonObject queryJS, int query_offset,int query_limit) {

		// define SelectQuery object
		SelectQuery sq = new SelectQuery();
		sq.addCustomColumns(new CustomSql("id"),new CustomSql("platform_id"),new CustomSql("class_code"),
				new CustomSql("class_name"),new CustomSql("pcode"),new CustomSql("class_level"),new CustomSql("type_name"),
				new CustomSql("is_delete"),
				new CustomSql("create_user"),new CustomSql("update_user"),new CustomSql("create_time"),
				new CustomSql("update_time") )
		    .addCustomFromTable("goods_class");

		//common part to add InCondition
		Iterator<String> iter =   queryJS.fieldNames().iterator();
		while (iter.hasNext()) {
		   String key = iter.next();
			try {
			  String value = queryJS.getString(key);
			  if( !value.equals("") && !value.equals("[]") ) {
				  String firstChar = value.substring(0,1);
				  if(firstChar.equals("["))
					  value = value.substring(1,value.length()-1);
				  value = value.replace(", ", "','");
				  //添加where  InCondtion 子句
				  sq.getWhereClause().addCustomCondition(new InCondition( new CustomSql(key),  value));
			  }

			} catch (Exception e) {
				// Something went wrong!
			}
		}
	   	return sq.validate().toString() + " order by id desc  limit " + query_limit + " offset " + query_offset ;
	}


	//插入新的数据
	public Future<RowSet<Row>> insertGoodsClass(SqlConnection conn,  String class_code, String class_name,  int class_level, String pcode, String type_name, String create_user){
		Promise<RowSet<Row>> promise = Promise.promise();
		String sql = "INSERT INTO goods_class(class_code, class_name, class_level,  pcode, type_name, create_user)"
				+ " values ($1, $2, $3, $4, $5, $6) RETURNING id " ;
	    conn
	      .preparedQuery(sql)
	      .execute(Tuple.of( class_code, class_name, class_level, pcode, type_name, create_user), ar2 -> {
	        //Release the connection to the pool
	        conn.close();
	        if(ar2.succeeded()){
	          logger.info("insertGoodsClass, insert success: "+ ar2.result().rowCount());
	          promise.complete(ar2.result());
	        }else{
	        	logger.info("insertGoodsClass, insert Failed");
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}



	//重载方法1:
	public  Future<RowSet<Row>> selectGoodsClassesByCodeList(SqlConnection conn, Set<String> code_list_set, int class_level){
		Promise<RowSet<Row>> promise = Promise.promise();
		String sql = "select id,class_code,class_name,class_level,pcode from goods_class where class_code = any(array['" +
	    		String.join("','", code_list_set) + "']) and class_level=$1 and is_delete=0";
	    conn
	      .preparedQuery(sql)
	      .execute(Tuple.of(class_level), ar2 -> {
	        //Release the connection to the pool
	        //conn.close();
	        if(ar2.succeeded()){
	          logger.info("selectGoodsClassesByCodeList, query success: "+ ar2.result().rowCount());
	          promise.complete(ar2.result());
	        }else{
	        	logger.info("selectGoodsClassesByCodeList, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}

	public  Future<RowSet<Row>> selectGoodsClassesByCodeList(SqlConnection conn, String sql, int class_level){
		Promise<RowSet<Row>> promise = Promise.promise();
	    conn
	      .preparedQuery(sql)
	      .execute(Tuple.of(class_level), ar2 -> {
	        //Release the connection to the pool
	        conn.close();
	        if(ar2.succeeded()){
	          logger.info("selectGoodsClassesByCodeList, query success: "+ ar2.result().rowCount());
	          promise.complete(ar2.result());
	        }else{
	        	logger.info("selectGoodsClassesByCodeList, 失败:"+ar2.cause().getMessage());
	        	//这里就相当于继续向上抛出异常，用Promise来向上抛异常
	        	promise.fail(ar2.cause());
	        }
	      });
	    return promise.future();
	}

}
